package capimServer;

import java.sql.*;

/**
 * Database class
 * @author danielm pessoafpc
 *
 */
public class DB {

	/**
	 * Start the database - creating tables and dropping existent ones
	 * @throws Exception
	 */
	public void startDB() throws Exception {
	      Class.forName("org.sqlite.JDBC");
	      Connection conn = DriverManager.getConnection("jdbc:sqlite:capim.db");
	      Statement stat = conn.createStatement();
	      conn.setAutoCommit(true);
	      stat.executeUpdate("drop table if exists users;");
	      stat.executeUpdate("create table users (id INTEGER CONSTRAINT PK PRIMARY KEY, user VARCHAR(50), password VARCHAR(50));");
	      
	      stat.executeUpdate("drop table if exists friendOf;");
	      stat.executeUpdate("create table friendOf (uId INTEGER, friendId INTEGER, "+
	    		  			 "CONSTRAINT PK PRIMARY KEY (uId,friendId), "+ 
	    		  			 "CONSTRAINT FK1 FOREIGN KEY (uId,friendId) REFERENCES users (id,id) ON DELETE CASCADE);");
	      
	      PreparedStatement prep = conn.prepareStatement(
	      "insert into users values (NULL, ?, ?);");

	      // test users
	      prep.setString(1,"Ze");
	      prep.setString(2,"abc");
	      prep.addBatch();
	      prep.setString(1, "Sa");
	      prep.setString(2, "123");
	      prep.addBatch();
	      prep.setString(1, "Silva");
	      prep.setString(2, "abc123");
	      prep.addBatch();

	      conn.setAutoCommit(false);
	      prep.executeBatch();
	      conn.setAutoCommit(true);

	      ResultSet rs = stat.executeQuery("select * from users;");
	      while (rs.next()) {
	      	System.out.println("User = " + rs.getString("user")+", Pass = " + rs.getString("password"));
	      }
	      rs.close();
	      conn.close();
	      
	  }
	
	
	/**
	 * Check if the user and login suplied exists in the DB
	 * @param userName user name
	 * @param password password form user
	 * @return true if the user and password match or false if not 
	 */
	public static boolean checkUser(String userName, String password){
		Connection conn = null;
		boolean returnVariable = false;
		try {
			Class.forName("org.sqlite.JDBC");
			conn = DriverManager.getConnection("jdbc:sqlite:capim.db");
		    conn.setAutoCommit(false);
		    
		    // Debug only
		    // System.out.println("Usuario->"+userName+":pass->"+password);
		    
		    PreparedStatement prep = conn.prepareStatement("SELECT * FROM users u WHERE u.user = ? AND u.password = ?");
		    prep.setString(1, userName);
		    prep.setString(2, password);
		    ResultSet rs = prep.executeQuery();
		    if(rs.next()){
		    	returnVariable = true;
		    } else returnVariable = false;
		    rs.close();
		    
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e1) {
			e1.printStackTrace();
		} catch (ArrayIndexOutOfBoundsException e2) {
			e2.printStackTrace();
		} finally {
			if(conn != null)
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
		}
		
		return returnVariable;
	}

}
